package cf.luobo.covid_19.dao;

import cf.luobo.covid_19.javaBean.pojo.ErrMsg;
import cf.luobo.covid_19.javaBean.pojo.Province;
import cf.luobo.covid_19.utils.MyJDBC;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProvinceDao {
    private Connection connection;
    private Statement statement;                    //静态SQL操作
    private PreparedStatement preparedStatement;    //动态SQL操作

    //查询全部省份
    public List<Province> allList(Date date_in) {
        List<Province> list = new ArrayList<>();
        ResultSet resultSet = null;

        try {
            //链接数据库
            connection = MyJDBC.getConnection();
            //获取操作数据库的对象Statement
            statement = connection.createStatement();
            //操作数据库
            for (int i = 0; i < Province.provides.length; i++) {
                String sql = String.format("SELECT date,currentConfirmedCount,cured,dead,suspected " +
                        "FROM %s WHERE date = ?", Province.provides[i]);
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setDate(1, date_in);
                resultSet = preparedStatement.executeQuery();

                //处理结果集
                while (resultSet.next())    //判断是否存在下一条数据
                {
                    int currentConfirmedCount = resultSet.getInt("currentConfirmedCount");
                    int dead = resultSet.getInt("dead");
                    int suspected = resultSet.getInt("suspected");
                    int cured = resultSet.getInt("cured");
                    Province province = new Province(Province.provides[i], currentConfirmedCount, cured, dead, suspected);
                    list.add(province);
                }
                preparedStatement.clearBatch();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MyJDBC.close(resultSet, statement, connection);   //关闭数据库
        }
        return list;
    }

    //按照省份查询
    public Province selectPro(String name_in, Date date_in) {
        Province province = null;
        String table = Province.engToChs.getKey(name_in);
        String sql = String.format("SELECT date,currentConfirmedCount,cured,dead,suspected FROM %s WHERE date = ?", table);
        ResultSet resultSet = null;
        try {
            connection = MyJDBC.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setDate(1, date_in);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                int currentConfirmedCount = resultSet.getInt("currentConfirmedCount");
                int dead = resultSet.getInt("dead");
                int suspected = resultSet.getInt("suspected");
                int cured = resultSet.getInt("cured");
                province = new Province(table, currentConfirmedCount, cured, dead, suspected);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MyJDBC.close(resultSet, preparedStatement, connection);
        }
        return province;
    }

    //修改
    public ErrMsg updateProvince(Province province, Date date_in) {
        ErrMsg err = new ErrMsg(404, "Unknown Error");
        String sql = String.format("UPDATE %s SET currentConfirmedCount = ?, " +
                "cured = ?, dead = ?, suspected = ? WHERE DATE = ?", province.getName());
        try {
            connection = MyJDBC.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, province.getCurrentConfirmedCount());
            preparedStatement.setInt(2, province.getCured());
            preparedStatement.setInt(3, province.getDead());
            preparedStatement.setInt(4, province.getSuspected());
            preparedStatement.setDate(5, date_in);
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("有" + i + "条数据修改成功...");
                err.setErrcode(0);
                err.setErrmsg("OK");
            } else {
//                err.setErrmsg("数据库中无此条数据，无法修改，请使用插入");
                //更新失败多半是没有数据，转为插入
                err = saveProvince(province, date_in);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MyJDBC.close(preparedStatement, connection);
        }
        return err;
    }

    //插入
    public ErrMsg saveProvince(Province province, Date date_in) {
        ErrMsg err = new ErrMsg(404, "Unknown Error");
        String sql = String.format("INSERT %s(currentConfirmedCount,cured,suspected,dead,date) " +
                "VALUES(?,?,?,?,?)", province.getName());
        try {
            connection = MyJDBC.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, province.getCurrentConfirmedCount());
            preparedStatement.setInt(2, province.getCured());
            preparedStatement.setInt(3, province.getDead());
            preparedStatement.setInt(4, province.getSuspected());
            preparedStatement.setDate(5, date_in);
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("有" + i + "条数据插入成功...");
                err.setErrcode(0);
                err.setErrmsg("OK");
            } else {
                err.setErrmsg("插入失败");
                err.setErrcode(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MyJDBC.close(preparedStatement, connection);
        }
        return err;

    }

}
